package com.zlq.javaWeb3.dao;

import com.zlq.javaWeb3.entity.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDao {
    private Connection con;

    public UserDao() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            con = DriverManager.
                    getConnection("jdbc:mysql://localhost:3306/stu?serverTimezone=UTC",
                            "root", "123456");
        } catch (Exception e) {
        }
    }

    public List<User> selectAll() {
        List<User> users = new ArrayList<>();
        String sql = "select * from muser";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString("name");
                int age = rs.getInt("age");
                User user = new User(id, name, age);
                users.add(user);
            }
            ps.close();
            rs.close();
        } catch (Exception e) {

        }
        return users;
    }

    public List<User> selectByPage(int numpage, int size) {
        //分页查询的sql
        String sql = "select * from muser limit ?,?";
        List<User> users = new ArrayList<>();
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, (numpage - 1) * size);
            ps.setInt(2, size);
//            (numpage-1)*size +size
//            ps.setInt(1, 0);
//            ps.setInt(2, 4);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                User user = new User(id, name, age);
//                System.out.println(user);
                users.add(user);
            }
            ps.close();
            rs.close();
        } catch (Exception e) {
        }
        return users;
    }


    public int selectcount() {
        String sql = "select count(id) from muser ";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            int count = 0;
            while (rs.next()) {
                count = rs.getInt(1);
            }
            ps.close();
            rs.close();
            return count;
        } catch (Exception e) {
        }
        return 0;
    }
}
